package com.example.victory.controller;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
@RestController

/*
 *表示该controller类下所有的方法都公用的一级上下文根
 */
@RequestMapping("/user")
public class Mysql {

    static String driver="com.mysql.jdbc.Driver";
    static String url="jdbc:mysql://localhost:3306/DB";
    static String use="root";
    static String password="cml132536";
    static int number;
    static Connection conn;

    public static void displayShelfUI() {
        System.out.println("欢迎使用图书管理系统!");
        System.out.println("请输入数字 : ");
        System.out.println("1) 查看图书");
        System.out.println("2) 增加图书");
        System.out.println("3) 修改图书");
        System.out.println("4) 删除图书");
        System.out.println("5) 导出数据");
        System.out.println("6) 导入数据");
    }

    public static void queryBook() throws Exception {
        System.out.println("查看图书");
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id, sname, price FROM book;");
        //如果有数据，rs.next()返回true
        while(rs.next()){
            System.out.println("id: " + rs.getInt("id")
                    + " 书名: " + rs.getString("sname")
                    + " 价格："+rs.getInt("price"));
        }
    }

    public static void insertBook() throws Exception {
        System.out.println("增加图书");
        Scanner ms = new Scanner(System.in);
        System.out.println("请输入id:");
        String number = ms.next();
        System.out.println("请输入sname:");
        String sname = ms.next();
        System.out.println("请输入price:");
        int price = ms.nextInt();
        Statement stmt = conn.createStatement();
        String sql = "insert into book  values ("+number+",\"" + sname
                + "\", " + price + ");";
        System.out.println("sql 语法: " + sql);
        stmt.execute(sql);

    }

    public static void updateBook() throws Exception {
        updateBook();
        System.out.println("修改图书");
        Statement stmt = conn.createStatement();
        Scanner ms = new Scanner(System.in);
        System.out.println("请输入id:");
        int id = ms.nextInt();
        System.out.println("请输入sname:");
        String sname = ms.next();
        System.out.println("请输入price:");
        int price = ms.nextInt();
        String sql = "update book set price = " + price
                + ", sname = \"" + sname
                + "\" where id = " + id;
        System.out.println("sql 语法: " + sql);
        stmt.execute(sql);
    }

    public static void deleteBook() throws Exception {
        System.out.println("已经输入4");
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        stmt.execute("delete from book");
    }

    public static void exportBookData()  throws Exception {
        System.out.println("导出数据");
        String output = "";
        output = output + "id\t书名\t价格\n\t";

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id, sname, price FROM book;");
        //如果有数据，rs.next()返回true
        while(rs.next()){
            output = output + rs.getInt("id") + "\t"
                    + rs.getString("sname") + "\t"
                    +rs.getInt("price") + "\n\t";
        }
//      System.out.println(output);
        try {
            BufferedWriter out = new BufferedWriter(new FileWriter("e:\\book.txt"));
            out.write(output);
            out.close();
        } catch (Exception e) {
            System.out.println("就知道会出现问题");
            System.out.println(e);
            return;
        }
        System.out.println("成功导出数据!");
    }

    public static void importBookData() {
        System.out.println("导入数据");
    }

    public static void main(String args[]) throws Exception
    {
        displayShelfUI();
        Scanner ms = new Scanner(System.in);
        //1.加载驱动程序
        Class.forName(driver);
        //2. 获得数据库连接
        conn = DriverManager.getConnection(url, use, password);

        while (true) {
            int a = ms.nextInt();

            switch(a) {
                case 1:
                    queryBook();//查看图书
                    break;
                case 2:
                    insertBook();//增加图书
                    break;
                case 3:
                    updateBook();//修改图书
                    break;
                case 4:
                    deleteBook();//删除图书
                    break;
                case 5:
                    exportBookData();//导出数据
                    break;
                case 6:
                    importBookData();//导入数据
                    break;
            }
        }
    }
}
/**
 * #查数据
 select * from test;  #取所有数据
 select * from test limit 0,2;  #取前两条数据
 select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个
 select * from test order by id asc;#降序desc
 select * from test id not in('2','3');#id不含2,3或者去掉not表示含有
 select * from test timer between 1 and 10;#数据在1,10之间

 #---------------------------表连接知识------------------------------
 #等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行
 select * from A inner join B on A.id = B.id; #写法1
 select * from A,B where A.id = B.id; #写法2
 select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称
 select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句

 #左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录
 select * from A left join B on A.id = B.id;

 select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接

 #右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录
 select * from A right join B on A.id = B.id;

 #完整外部链接 full join 返回左右表中所有数据
 select * from A full join B on A.id = B.id;

 #交叉连接 没有where字句 返回卡迪尔积
 select * from A cross join B;
 -------------------------表连接结束------------------------------------------------------------
 -----------------索引创建------------------------------------------------
 show index from A #查看索引
 alter table A add primary key(id) #主键索引
 alter table A add unique(name) #唯一索引
 alter table A add index name(name) #普通索引
 alter table A add fulltext(name) #全文索引
 alter table A add index name(id,name) #多列索引

 #常用函数
 abs(-1)#绝对值
 pi()#pi值
 sqrt(2)#平方根
 mod(-5,3)#取余-2
 ceil(10.6)#进位+1 结果11 ceil(10.0)结果10
 floor(10.6)#取整 10
 round(2.5)#四舍五入到整数 结果3
 round(2.5,2)#保留两位小数 结果2.50
 truncate(2.5234,3)#取小数后3位不四舍五入 2.523
 sign(-2);#符号函数 返回-1 0还是0 正数返回1
 pow(2,3),exp(2);#2的3次幂 或e的2次幂
 log(2),log10(2);#求对数
 radians(180),degrees(0.618);#角度弧度转换
 sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atan
 length('hi')#计算字符长度
 concat('1',1,'hi')#合并字符串
 insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入
 ucase('a'),lcase('A')#转成大写和小写
 left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符
 ltrim('  0  '),rtrim(' 0 '),trim('  0  ')#删除空格
 replace('1234567890','345678','0');#替换输出12090
 substring('12345',1,2)#取字符 输出12 1是位置 2是长度
 instr('1234','234');#取得234位置是2
 reverse('1234');#反序输出4321
 current()#返回日期
 curtime()#返回时间
 now()#返回日期时间
 month(now())#当前月份 monthname 英文月份
 dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二
 week(now())#本年第多少周
 dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天
 year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒
 time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原
 version()#mysql版本
 database()#当前连接的数据库 没有为null
 user()#获取用户名
 md5('a')#加密字符串
 ascii('a')#ascii值97
 bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制
 conv(10001,2,8);#各种进制相互转换
 rand()#生成0到1之间随机数
 sleep(0.02)#暂停秒数
 ---------------------------------
 */